package com.OnlineHouseLeaseSystem.common;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

import com.OnlineHouseLeaseSystem.database.DBManager;

public class HouseCommentManager {
	private static final String COMMENT_ID = "comment_id";
	private static final String COMMENT_FOR_HOUSE = "comment_for_house";
	private static final String USER_ID = "user_id";
	private static final String USER_NAME = "user_name";
	private static final String HOUSE_ID = "house_id";
	
	private final static String LOCK_TABLE = "LOCK TABLE house_comment IN EXCLUSIVE MODE; ";
	
	private final static String GET_HOUSE_COMMENT_AND_USER = "SELECT * FROM house_comment,user_information" +
															 " WHERE house_comment.user_ID=user_information.user_id" +
															 " ORDER BY comment_id ";
	private final static String GET_MAX_COMMENT_ID = "SELECT MAX(comment_id) FROM house_comment";
	private final static String INSERT_COMMENT_HEAD = "INSERT INTO house_comment VALUES ( '";
    private final static String INSERT_COMMENT_FOOT = "' ) ";
    private final static String GET_HOUSE_COMMENT = "SELECT * FROM house_comment ORDER BY comment_id DESC";
    private final static String GET_HOUSE_COMMENT_AND_USER_INFORMATION = 
    							"SELECT * FROM house_comment,user_information" +
    							" WHERE house_comment.user_id=user_information.user_id" +
    							" ORDER BY comment_id DESC";
    private final static String DELETE_HOUSE_COMMENT = "DELETE FROM house_comment WHERE comment_id='";
    
    public static ArrayList getUserHouseComment(int user_id)
    {
    	ArrayList list = new ArrayList();
    	try
    	{
    		DBManager dbManager = new DBManager();
    		ResultSet result = dbManager.executeQuery(GET_HOUSE_COMMENT_AND_USER_INFORMATION);
    		while(result.next())
    		{
    			if(result.getInt("user_id") == user_id)
    			{
    				HouseComment houseComment = new HouseComment();
        			houseComment.setUser_id(result.getInt(USER_ID));
        			houseComment.setComment_id(result.getInt(COMMENT_ID)); 
        			houseComment.setComment_for_house(result.getString(COMMENT_FOR_HOUSE)); 
        			houseComment.setHouse_id(result.getInt(HOUSE_ID));
        			houseComment.setUser_name(result.getString(USER_NAME));
                    list.add(houseComment);
    			}
    		}
    		dbManager.close();
    	}
    	catch (SQLException ex) 
    	{
            Logger.getLogger(HouseManager.class.getName()).log(Level.SEVERE, null, ex);
        }
    	return list;
    }
    
    public static void deleteHouseComment(int comment_id)
	{
		try
    	{
    		DBManager dbManager = new DBManager();
    		dbManager.executeUpdate(DELETE_HOUSE_COMMENT + comment_id + "'");
    		dbManager.close();
    	}
    	catch (SQLException ex)
    	{
    		Logger.getLogger(UserManager.class.getName()).log(Level.SEVERE, null, ex);
    	}
	}
    
    public static ArrayList getAllHouseComment()
    {
    	ArrayList list = new ArrayList();
    	try
    	{
    		DBManager dbManager = new DBManager();
    		ResultSet result = dbManager.executeQuery(GET_HOUSE_COMMENT);
    		while(result.next())
    		{
    			HouseComment houseComment = new HouseComment();
    			houseComment.setUser_id(result.getInt(USER_ID));
    			houseComment.setComment_id(result.getInt(COMMENT_ID)); 
    			houseComment.setComment_for_house(result.getString(COMMENT_FOR_HOUSE)); 
    			houseComment.setHouse_id(result.getInt(HOUSE_ID));
                list.add(houseComment);
    		}
    		dbManager.close();
    	}
    	catch (SQLException ex) 
    	{
            Logger.getLogger(HouseManager.class.getName()).log(Level.SEVERE, null, ex);
        }
    	return list;
    }
	
	public static ArrayList getHouseComment(int house_id)
	{
		ArrayList list = new ArrayList();		
    	try
    	{
    		DBManager dbManager = new DBManager();
    		ResultSet result = dbManager.executeQuery(GET_HOUSE_COMMENT_AND_USER);    		
    		while(result.next())
    		{
    			if(result.getInt(HOUSE_ID) == house_id)
    			{   
    				HouseComment houseComment = new HouseComment();
    				houseComment.setHouse_id(result.getInt(HOUSE_ID));
    				houseComment.setComment_id(result.getInt(COMMENT_ID));
    				houseComment.setUser_id(result.getInt(USER_ID));
    				houseComment.setUser_name(result.getString(USER_NAME));
    				houseComment.setComment_for_house(result.getString(COMMENT_FOR_HOUSE));
    				list.add(houseComment);
    			}                
    		}
    		dbManager.close();
    	}
    	catch (SQLException ex) 
    	{
            Logger.getLogger(HouseManager.class.getName()).log(Level.SEVERE, null, ex);
        }
    	return list;
	}
	
	public static int getComment_idForNew()
    {
        int maxUid = 0;
        try
        {
            DBManager dbManager = new DBManager();
            ResultSet result = dbManager.executeQuery(LOCK_TABLE+GET_MAX_COMMENT_ID);
            result.next();
            maxUid = result.getInt(1);
            dbManager.close();
        }
        catch (SQLException ex)
        {
            Logger.getLogger(UserManager.class.getName()).log(Level.SEVERE, null, ex);
        }
        return maxUid+1;
    }
    
    public static void insertNewHouse(int user_id, int house_id, String comment_for_house)
    {
    	int comment_id = getComment_idForNew();
    	try
    	{
    		DBManager dbManager = new DBManager();
    		dbManager.executeUpdate(INSERT_COMMENT_HEAD +
    				user_id + "','" + house_id + "','" + comment_id + "','" + comment_for_house + INSERT_COMMENT_FOOT);
    		dbManager.close();
    	}
    	catch (SQLException ex)
    	{
    		Logger.getLogger(HouseManager.class.getName()).log(Level.SEVERE, null, ex);
    	}
    }
}
